#============================================
# #知识点
# 表准备

#修改编码
#个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象你可以将字符编码
# 统一设置成GBK
# set character_set_client = 'gbk' ;
# set character_set_connection = 'gbk' ;
# set character_set_results= 'gbk' ;
# set character_set_server= 'gbk' ;
# set character_set_database= 'gbk' ;


# create table emp(
#     id int not null unique auto_increment,
#     name varchar(20) not null,
#     sex enum('male','female') not null default 'male',
#     age int(3) unsigned not null default 28,
#     hire_date date not null,
#     post varchar(50),
#     post_comment varchar (100),
#     salary double(15,2),
#     office int,
#     depart_id int
#     );


# insert into
# emp(name,sex,age,hire_date,post,salary,office,depart_id) values
# ('jason', 'male' ,18,'20170301','张江第一帅形象代言' ,7300.33,401,1),
# ('tom','male' ,78,' 20150302','teacher' ,1000000.31, 401,1),
# ('kevin','male' ,81, '20130305','teacher' ,8300 ,401,1),
# ('tony', 'male' ,73, ' 20140701', 'teacher',3500,401,1),
# ('owen', 'male' ,28,'20121101' ,'teacher' ,2100 ,401,1),
# ('jack','female' ,18, '20110211','teacher' ,9000,401,1),
# ('jenny', 'male' ,18,'19000301','teacher' ,30000,401,1),
# ('sank', 'male' ,48, ' 20101111','teacher' ,10000 ,401,1),
# ('哈哈','female' ,48, ' 20150311', 'sale',3000.13 ,402,2),
# ('呵呵','female' ,38,'20101101','sale' ,2000.35,402,2),
# ('西西','female' ,18, ' 20110312','sale' ,1000.37 ,402,2),
# ('乐乐','female' ,18, '20160513','sale' ,3000.29,402,2),
# ('拉拉','female' ,28,' 20170127', 'sale' ,4000.33 ,402,2),
# ('僧龙','male' ,28,' 20160311', 'operation',10000.13,403,3), 
# ('程咬金','male' ,18,' 19970312',' operation' ,20000,403,3),
# ('程咬银','female' ,18,' 20130311', ' operation' ,19000,403,3),
# ('程咬铜','male' ,18, ' 20150411', ' operation' ,18000,403,3),
# ('程咬铁' , 'female' ,18, ' 20140512', 'operation',17000 ,403,3);
#当用select * from 表 数据太多，展示错乱，可以在后面加上\G就可以一行一行显示。
#============================================
# 几个重要关键字的执行顺序
#书写顺序
# select id,name from emp where id > 3;
#执行顺序
# from
# where
# select

# 虽然执行顺序和书写顺序不一-致你在写sq1语句的时候可能不知道怎么写
# 你就按照书写顺序的方式写sq1
# select *先用*号占位
# 之后去补全后面的sq1语句
# 最后将*号替换后你想要的具体字段
#============================================
# where  条件
# 1.查询id大于等于3小于等于6的数据
# select id,name,age from emp where id>=3 and id<=6;1

# 2.查询薪资是20000或者18000或者17000的数据
# select * from emp where saTary=20000 or salary=18000 or salary=17000;
# se1ect * from emp where salary in (20000 ,18000，17000) ;

# 3.查询员工姓名中包含字母o的员工的姓名和薪资
# 模糊查询
# like
# % 匹配任意多 个字符
# _ 匹配任意单个字符
# select name,salary from emp where name 1ike'o%';

# 4.查询员工姓名是由四个字符组成的姓名和薪资
# select name,salary from emp where name like "____";
# select name,salary from emp where char_length(name) = 4;

# 5.查询id小于3或者id大于6的数据
# select * from emp where id not between 3 and 6;

# 6.查询薪资不在20000 , 18000, 17000范围的数据
# select * from emp where salary not in (20000 ,18000, 17000)

# 7.查询岗位描述为空的员工姓名和岗位名针对nu11 不用等号 用is
# select name ,post from emp where post_comment = NULL; 错误的
# select name,post from emp where post_comment is NULL;
#============================================
# group by 分组 (技巧看题，每个xxx就是按照xxx分组)
# 实际应用场景:
# 男女比列
# 部门平均薪资
# 国家之间数据统计

# 分组之后最小可操作单位应该是组还不再是组内的单个数据
# 上述命令在你没有设置严格模式的时候是可正常执行的返回的是分组之后每个组的
# 第一.条数据但是这不符合分组的规范:分组之后不应该考虑单个数据而应该以组为操作
# 单位(分组之后没办法直接获取组内单个数据)

# 如果设置了严格模式那么上述命令会直接报错
# set global sql_mode = "strict_trans_tables,only_full_group_by";

# 设置严格模式之后分组 默认只能拿到分组的依据
# select * from emp group by post;  按照什么 分组就只能拿到分组 其他字段不能直接获取，需要借助一些方法(聚合函数)

# --------------------------------------------
#练习1
# 1.获取每个部门的最高薪资
# select post,max(salary) from emp group by post;
# select post as "部门",max(salary) as "最大薪资" from emp group by post;  as可以给字段起别名，也可以省略

# 2.获取每个部门的最低薪资
# select post as "部门",min(salary) as "最低薪资" from emp group by post;

# 3.获取每个部门的平均薪资
# select post as "部门",avg(salary) as "平均薪资" from emp group by post;

# 4.获取每个部门的工资总和
# select post as "部门",sum(salary) as "工资总和" from emp group by post;

# 5.获取每个部门的人数 I
# select post as "部门",count(post) as "人数" from emp group by post;   count无法对null进行计数

# 6.查询分组之后的部门名称和每个部门下所有的员工姓名
# group_ concat不单单可以支持你获取分组之后的其他字段值还支持拼接操作
# select post,group_concat(name) from emp group by post;
# select post,group_concat("员工",":",name) from emp group by post;
# select post,group_concat("员工",":",name,",","薪资",salary) from emp group by post;
# concat 不分组的时候用
# select concat(' NAME:',name),concat('SAL:',salary) from emp ;
#concat__ws:如果多个字段之间的连接符号是相同的情况下你可以
# 直接使用concat_ws 来完成
# select concat_ws(':' ,name ,age,sex)

# 7.查询每个人的年薪 12薪
# select name,salary*12 from emp;

# --------------------------------------------
#分组注意事项
#关键字where 和group by同时出现的时候group by必须在where的后面
# where先对整体数据进行过滤之后再分组操作
# where筛选条件不能使用聚合函数
# select id,name,age from emp where max(salary) > 3000; 错误
# select max(salary) from emp; 不分组默认整体就是一组

#统计各部门年龄在30岁以上的员工平均薪资
# select post as "部门",group_concat(name,":",salary ) as "大于30岁员工薪资列表" from emp where age >=30 group by post;
# select post as "部门",sum(salary) as "大于三十岁员工总薪资" from emp where age >=30 group by post;
# select post as "部门",avg(salary) as "大于三十岁员工总平均薪资" from emp where age >=30 group by post;
#总结: select 分组字段,聚合函数 from 表 where 条件 group 分组字段(分组依据)
#============================================
# having分组之后的筛选条件
# having的语法根where是一致的
# 只不过having是在分组之后进行的过滤操作
# 即having是可以直接使用聚合函数的
# having放在最后添加

#统计各部门年龄在30岁以上的员工平均工资 并且保留平均薪资大于10000的部门
# select post,avg(salary) from emp where age >=30 group  by post having avg(salary) >10000;

#============================================
# distinct 去重
# 一定要注意必须是完全-一样的数据才可以去重! ! !  放在查询对象前面
# 一定不要将逐渐忽视了 有逐渐存在的情况下是不可能去重的
# {'id' :1, 'name' :'jason','age' :18},
# {'id' :2, 'name' :'jason','age' :18},
# {'id' :3，'name': 'egon', ' age' :18}
# ]
# select distinct id,age from emp;(id值不可能一样，所以不能去重)
# select distinct age from emp;
#============================================
# order by 排序
# select * from emp order by salary;
# select * from emp order by salary desc;
#默认为升序，加desc为降序
#select * from emp order by age,salary desc;
#可以指定多个条件排序，   从左往右 age升序 ——》salary降序
#统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门，然后对平均工资降序排序
# select distinct 
#   post,avg(salary) from emp 
#   where age>10 
#   group by post 
#   having avg(salary)>1000 
#   order by avg(salary) desc;
#============================================
# limit限制展示条数
# """针对数据过多的情况我们通常都是做分页处理""" 
# select * from emp limit 10,4;   第一个数为起始位置，第二个数为取多少个
# select * from emp limit 5;   取5个
#============================================
# 正则
# select * from emp where name regexp '^j. *(n|y)$' ;
#============================================
